-- =============================================
-- delete storeprocedures
-- =============================================

-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME = N'ECommerce_Address_Delete' 
)
   DROP PROCEDURE ECommerce_Address_Delete
GO

CREATE PROCEDURE ECommerce_Address_Delete
	@AddressId	int
AS
	BEGIN TRANSACTION

	DELETE FROM [dbo].[Address]
    WHERE AddressId=@AddressId

    IF( @@ERROR <> 0 )
    BEGIN
		ROLLBACK TRANSACTION
		RETURN 0
	END
	ELSE
	BEGIN
		COMMIT TRANSACTION
		RETURN 1
	END
GO


-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME = N'ECommerce_Category_Delete' 
)
   DROP PROCEDURE ECommerce_Category_Delete
GO

CREATE PROCEDURE ECommerce_Category_Delete
	@CategoryID	int
AS
	BEGIN TRANSACTION

	DELETE FROM [dbo].[Product_Category_Mapping]
	WHERE CategoryID=@CategoryID

	DELETE FROM [dbo].[Category]
    WHERE CategoryID=@CategoryID

    IF( @@ERROR <> 0 )
    BEGIN
		ROLLBACK TRANSACTION
		RETURN 0
	END
	ELSE
	BEGIN
		COMMIT TRANSACTION
		RETURN 1
	END
GO


-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME = N'ECommerce_Document_Delete' 
)
   DROP PROCEDURE ECommerce_Document_Delete
GO

CREATE PROCEDURE ECommerce_Document_Delete
	@DocumentID	int
AS
	BEGIN TRANSACTION

	DELETE FROM [dbo].[Document]
    WHERE DocumentID=@DocumentID

    IF( @@ERROR <> 0 )
    BEGIN
		ROLLBACK TRANSACTION
		RETURN 0
	END
	ELSE
	BEGIN
		COMMIT TRANSACTION
		RETURN 1
	END
GO


-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME = N'ECommerce_Product_Delete' 
)
   DROP PROCEDURE ECommerce_Product_Delete
GO

CREATE PROCEDURE ECommerce_Product_Delete
	@ProductID	int
AS
	BEGIN TRANSACTION

	-- clear related products
	DELETE FROM [dbo].[RelatedProduct]
	WHERE ProductID1=@ProductID OR ProductID2=@ProductID
	
	-- clear product's picture
	DECLARE @TBPICTUREIDS TABLE (PictureID INT)
	
	DELETE FROM [dbo].[ProductPicture] 
	OUTPUT DELETED.PictureID
	INTO @TBPICTUREIDS
	WHERE ProductID=@ProductID

	DELETE FROM [dbo].[Picture]
    WHERE PictureID IN (SELECT PictureID FROM @TBPICTUREIDS)
    
	-- clear productreview
	DELETE FROM [dbo].[ProductReviewHelpfulness]
	WHERE ProductReviewID IN (SELECT ProductReviewID FROM [dbo].[ProductReview] WHERE ProductID = @ProductID)

	DELETE FROM [dbo].[ProductReview]
	WHERE ProductID = @ProductID

	-- clear product category mapping
	DELETE FROM [dbo].[Product_Category_Mapping]
	WHERE ProductID=@ProductID

	-- clear product tag mapping
	DELETE FROM [dbo].[ProductTag_Product_Mapping]
	WHERE ProductID=@ProductID

	-- clear product document mapping
	DELETE FROM [dbo].[Product_Document_Mapping]
	WHERE ProductID=@ProductID

	-- clear product additional property
	DELETE FROM [dbo].[ProductAdditionalProperty]
	WHERE ProductID=@ProductID
	
	-- clear shopping cart item
	DELETE FROM [dbo].[ShoppingCartItem]
	WHERE ProductID=@ProductID
	
	-- clear product_shippingmethod_mapping
	DELETE FROM [dbo].[Product_ShippingMethod_Mapping]
	WHERE ProductID=@ProductID
	
	-- clear product
	DELETE FROM [dbo].[Product]
	WHERE ProductID=@ProductID

    IF( @@ERROR <> 0 )
    BEGIN
		ROLLBACK TRANSACTION
		RETURN 0
	END
	ELSE
	BEGIN
		COMMIT TRANSACTION
		RETURN 1
	END
	
GO


-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME = N'ECommerce_ProductPicture_Delete' 
)
   DROP PROCEDURE ECommerce_ProductPicture_Delete
GO

CREATE PROCEDURE ECommerce_ProductPicture_Delete
	@PictureID	int,
	@ProductID	int
AS
	BEGIN TRANSACTION

	DELETE FROM [dbo].[Picture]
    WHERE PictureID=@PictureID

	DELETE FROM [dbo].[ProductPicture]
	WHERE PictureID=@PictureID AND ProductID=@ProductID

    IF( @@ERROR <> 0 )
    BEGIN
		ROLLBACK TRANSACTION
		RETURN 0
	END
	ELSE
	BEGIN
		COMMIT TRANSACTION
		RETURN 1
	END
    
GO


-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME = N'ECommerce_ProductReview_Delete' 
)
   DROP PROCEDURE ECommerce_ProductReview_Delete
GO

CREATE PROCEDURE ECommerce_ProductReview_Delete
	@ProductReviewID	int
AS
	BEGIN TRANSACTION

	DELETE FROM [dbo].[ProductReviewHelpfulness]
	WHERE ProductReviewID=@ProductReviewID

	DELETE FROM [dbo].[ProductReview]
	WHERE ProductReviewID=@ProductReviewID

    IF( @@ERROR <> 0 )
    BEGIN
		ROLLBACK TRANSACTION
		RETURN 0
	END
	ELSE
	BEGIN
		COMMIT TRANSACTION
		RETURN 1
	END

GO


-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME = N'ECommerce_ProductTag_Delete' 
)
   DROP PROCEDURE ECommerce_ProductTag_Delete
GO

CREATE PROCEDURE ECommerce_ProductTag_Delete
	@ProductTagID	int
AS
	BEGIN TRANSACTION

	DELETE FROM [dbo].[ProductTag_Product_Mapping]
	WHERE ProductTagID=@ProductTagID

	DELETE FROM [dbo].[ProductTag]
	WHERE ProductTagID=@ProductTagID

    IF( @@ERROR <> 0 )
    BEGIN
		ROLLBACK TRANSACTION
		RETURN 0
	END
	ELSE
	BEGIN
		COMMIT TRANSACTION
		RETURN 1
	END

GO


-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME = N'ECommerce_ProductDocument_Delete' 
)
   DROP PROCEDURE ECommerce_ProductDocument_Delete
GO

CREATE PROCEDURE ECommerce_ProductDocument_Delete
	@DocumentID	int
AS
	BEGIN TRANSACTION

	DELETE FROM [dbo].[Product_Document_Mapping]
	WHERE DocumentID=@DocumentID

    IF( @@ERROR <> 0 )
    BEGIN
		ROLLBACK TRANSACTION
		RETURN 0
	END
	ELSE
	BEGIN
		COMMIT TRANSACTION
		RETURN 1
	END

GO


-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME = N'ECommerce_RelatedProduct_Delete' 
)
   DROP PROCEDURE ECommerce_RelatedProduct_Delete
GO

CREATE PROCEDURE ECommerce_RelatedProduct_Delete
	@ProductID1	int,
	@ProductID2	int
AS
	BEGIN TRANSACTION

	DELETE FROM [dbo].[RelatedProduct]
	WHERE ProductID1=@ProductID1 AND ProductID2=@ProductID2 

    IF( @@ERROR <> 0 )
    BEGIN
		ROLLBACK TRANSACTION
		RETURN 0
	END
	ELSE
	BEGIN
		COMMIT TRANSACTION
		RETURN 1
	END

GO


-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME = N'ECommerce_ShoppingCartItem_Delete' 
)
   DROP PROCEDURE ECommerce_ShoppingCartItem_Delete
GO

CREATE PROCEDURE ECommerce_ShoppingCartItem_Delete
	@ShoppingCartItemID	int
AS
	BEGIN TRANSACTION

	DELETE FROM [dbo].[ShoppingCartItem]
	WHERE ShoppingCartItemID=@ShoppingCartItemID

    IF( @@ERROR <> 0 )
    BEGIN
		ROLLBACK TRANSACTION
		RETURN 0
	END
	ELSE
	BEGIN
		COMMIT TRANSACTION
		RETURN 1
	END

GO


-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME = N'ECommerce_Order_Delete' 
)
   DROP PROCEDURE ECommerce_Order_Delete
GO

CREATE PROCEDURE ECommerce_Order_Delete
	@OrderID	int
AS
	BEGIN TRANSACTION

	DELETE FROM [dbo].[OrderNote]
	WHERE OrderID=@OrderID
	
	DELETE FROM [dbo].[Order_Product_Mapping]
	WHERE OrderID=@OrderID

	DELETE FROM [dbo].[Order]
	WHERE OrderID=@OrderID

    IF( @@ERROR <> 0 )
    BEGIN
		ROLLBACK TRANSACTION
		RETURN 0
	END
	ELSE
	BEGIN
		COMMIT TRANSACTION
		RETURN 1
	END

GO

-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME = N'ECommerce_News_Delete' 
)
   DROP PROCEDURE ECommerce_News_Delete
GO

CREATE PROCEDURE ECommerce_News_Delete
	@NewsID	int
AS
	BEGIN TRANSACTION

	DELETE FROM [dbo].[News]
	WHERE NewsID=@NewsID

    IF( @@ERROR <> 0 )
    BEGIN
		ROLLBACK TRANSACTION
		RETURN 0
	END
	ELSE
	BEGIN
		COMMIT TRANSACTION
		RETURN 1
	END

GO

-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME = N'ECommerce_Language_Delete' 
)
   DROP PROCEDURE ECommerce_Language_Delete
GO

CREATE PROCEDURE ECommerce_Language_Delete
	@LanguageId	int
AS
	BEGIN TRANSACTION

	DELETE FROM [dbo].[Language]
    WHERE LanguageId=@LanguageId

    IF( @@ERROR <> 0 )
    BEGIN
		ROLLBACK TRANSACTION
		RETURN 0
	END
	ELSE
	BEGIN
		COMMIT TRANSACTION
		RETURN 1
	END
GO


-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME = N'ECommerce_LocalStringResource_Delete' 
)
   DROP PROCEDURE ECommerce_LocalStringResource_Delete
GO

CREATE PROCEDURE ECommerce_LocalStringResource_Delete
	@LocaleStringResourceID	int
AS
	BEGIN TRANSACTION

	DELETE FROM [dbo].[LocalStringResource]
    WHERE LocaleStringResourceID=@LocaleStringResourceID

    IF( @@ERROR <> 0 )
    BEGIN
		ROLLBACK TRANSACTION
		RETURN 0
	END
	ELSE
	BEGIN
		COMMIT TRANSACTION
		RETURN 1
	END
GO


-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME = N'ECommerce_PaymentMethod_Delete' 
)
   DROP PROCEDURE ECommerce_PaymentMethod_Delete
GO

CREATE PROCEDURE ECommerce_PaymentMethod_Delete
	@PaymentMethodID	int
AS
	BEGIN TRANSACTION

	DELETE FROM [dbo].[PaymentMethod_RestrictedCountries]
    WHERE PaymentMethodID=@PaymentMethodID

	DELETE FROM [dbo].[PaymentMethod]
    WHERE PaymentMethodID=@PaymentMethodID

    IF( @@ERROR <> 0 )
    BEGIN
		ROLLBACK TRANSACTION
		RETURN 0
	END
	ELSE
	BEGIN
		COMMIT TRANSACTION
		RETURN 1
	END
GO


-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME = N'ECommerce_Order_Product_Mapping_Delete' 
)
   DROP PROCEDURE ECommerce_Order_Product_Mapping_Delete
GO

CREATE PROCEDURE ECommerce_Order_Product_Mapping_Delete
	@OrderID	int,
	@ProductID	int
AS
	BEGIN TRANSACTION

	DELETE FROM [dbo].[Order_Product_Mapping]
    WHERE OrderID=@OrderID AND ProductID=@ProductID

    IF( @@ERROR <> 0 )
    BEGIN
		ROLLBACK TRANSACTION
		RETURN 0
	END
	ELSE
	BEGIN
		COMMIT TRANSACTION
		RETURN 1
	END
GO
